<?php
/*
*
* dtapi 数据查询类
* 项目官网： www.dtapi.cn
* 这是一个完全开源免费的项目，您可以任意使用；我们唯一的要求就是保留本信息，当然您也可以在后面添加自己的版权信息，我们支持任何人使用本软件进行任何商业的非商业用途。
* 如果您有问题或者有更好的想法可以加群 61291160 一起讨论。
* -- 高飞
*
*/
/*
 * 链式数据查询类，类似于thinkphp,有细区别，
 * 作者：高飞
 * 只支持查询，分别为数组集合查询和单条数据查询
 * 集合查询,支持自动分页，返回分页数组
 * 支持的方法
 * ->table('表名')  可以是多表联查,
 * ->moduleid(模块id)，如果是查询destoon各模块主表的信息，可以不需要使用table方法，直接使用些方法，传模块id即可
 * ->field('字段1，字段2')
 * ->where('areaid=1')  支持传入数组 如  $where = array(array("areaid=1",array("child=1")));  数组传入时，自动用AND连接
 * ->whereor("areaid=3") 同上，用OR 与前一个where()方法里的条件连接，支持传入数组,数组之间使用 AND连接,使用whereor时必须有where方法，否则无效
 * ->condition("areaid=3 and level>1") 原生SQL条件，优先级高于 where()方法，传入condition()方法，where()方法失效
 * ->group('title,lid') 分组，group by 方法
 * ->order('addtime desc,itemid desc,vip desc') 排序
 * ->page(1,10), 表示第一页，每页10条， 如只传一个参数，则默认为第1页，条数使用select类中默认的条数 20条，这两个参数不能用 单引号或双引号
 * ->showpage(),输出分页，不需要传参数，在模板中直接使用 {$pages} 则可使用分页了
 * ->showcat(),关联分类名称查询，不需要传参数，使用后在模板 循环中 使用{$t[catname]}调用分类名称 {$t[caturl]} 调用分类连接
 * ->db(数据库id),读写分离，多从库时使用，指选择指定的数据库，数据库在根目录db_config.php中配置
 * ->debug(); 不需要传参数，打印出select类组装的sql语句
 * ->cache(时间秒数) 指本次查询是否用缓存，缓存多少秒，使用该方法后，可以在下文中使用 $cacheid 全局变量调用缓存id
 *->get_lists(); 查询数据集，无参数，该方法必须放在链式表达式的最后面，与get_one();不能同时使用
 * get_one(); 查询单条数据，无参数，该方法必须放在链式表达式的最后面，与get-lists()不能同时使用
 *
 * 全局使用 $item = $select->table('area')->where("areaid=1")->get_one();
 * 或者
    $where = [];
    $where[] = "parentid=0";
    $data = $select->table('area')->order('listorder asc')->where($where)->page(1,10)->get_lists();

*
 *
 * $items 下文中获取get_lists() 方法返回的总数量
 */
namespace dtapi;

class Db
{
    public $table;
    public $field = '*';
    public $offset;
    public $pagesize = 20;
    public $pages;
    public $page = 1;
    public $order;
    public $cache;
    public $condition;
    public $where;
    public $groupby;
    public $limit = 0;
    public $dbid = 0;
    public $showcat;
    public $moduleid;
    public $debug;
    public $whereor;
    public $insert_id;
    public $content;
    public $updatesql;
    public $showpage;
    private $row;

    function __call($method,$args){
        global $DT_PRE,$CFG;
        $action = strtolower($method);

        switch ($action){
            case 'table':
                $this->table = $DT_PRE.$args[0];
                break;
            case 'where':
                if(is_array($args[0])){
                    foreach($args[0] as $k=>$v){
                        if(strlen($v)>3) $this->where[] = "AND $v";
                    }
                }else{
                    if(strlen($args[0])>3) $this->where[] = "AND $args[0]";
                }

                break;
            case 'whereor':
                if(is_array($args[0])){
                    foreach($args[0] as $k=>$v){
                        if(strlen($v)>3) $this->whereor[] = "AND $v";
                    }
                }else{
                    if(strlen($args[0])>3) $this->whereor[] = "AND $args[0]";
                }
                break;
            case 'condition':
                $this->condition = $args[0];
                break;
            case 'order':
                $this->order = 'order by '.$args[0];
                break;
            case 'group':
                $this->groupby = $args[0] ? "GROUP BY $args[0]" : "";
                break;
            case 'field':
                $this->field = $args[0];
                break;
            case 'page':
                if(count($args)>1){
                    $this->page = $args[0];
                    $this->pagesize = $args[1];

                }else{
                    $this->page = $args[0];
                }
                $this->limit = 1;
                break;
            case 'showpage':
                $this->showpage = 1;
                break;
            case 'cache':
                $this->cache = $args[0] ? intval($args[0]) : $CFG['db_expires'];
                break;
            case 'db':
                $this->dbid = $args[0] ? intval($args[0]) : 0;
                break;
            case 'showcat':
                $this->showcat = 1;
                break;
            case 'moduleid':
                $this->moduleid = $args[0] ? intval($args[0]) : 1;
                if(!$this->table && $this->moduleid>3){
                    $this->table = get_table($this->moduleid);
                }
                break;
            case 'debug':
                $this->debug = 1;
                break;
            case 'content':
                $field = isset($args[3]) ? $args[3] : '';
                if($field){
                    $field = strstr($field,',') ? explode(',',$field) : array($field);
                }
                $this->content = array(
                    'table'=>$DT_PRE.$args[0],
                    'key'=>$args[1],
                    'data'=>dhtmlspecialchars($args[2]),
                    'field'=>$field,
                    'replace'=> isset($args[4]) ? 1 : 0
                );
                break;
            case 'updatesql':
                $this->updatesql = $args[0];
                break;
            case 'insertall':
                $this->insertAll($args[0]);
                break;
            case 'row':
                $this->row = 1;
                break;


        }
        return $this;
    }

    private function sql(){
        $this->groupby = $this->groupby ? "group by {$this->groupby}" : "";
        $where = $whereor = "";
        if($this->where){
            foreach($this->where as $k=>$v){
                $nbsp = $k ? ' ' : '';
                $where .= $nbsp.$v;
            }
            $where = substr($where,3);
        }

        if($this->whereor && $where){
            foreach($this->whereor as $k=>$v){
                $nbsp = $k ? ' ' : '';
                $whereor .= $nbsp.$v;
            }
            $whereor = substr($whereor,3);
            $where .= " OR ($whereor)";
        }

        $this->condition = $this->condition ? $this->condition : $where;
        // return true;
    }

    public function get_lists(){
        global $cacheid,$DT_PRE,$pages,$MODULE,$db,$items;
        $this->sql();

        $lists = $catids = $CATS = array();
        $cache = $this->cache ? "CACHE" : '';
        $ttl = $this->cache ? $this->cache : 0;
        if($this->showpage){
            if($this->groupby){
                $t = \DB::get_one("select count(num) as items from (select count(*) as num from {$this->table} where {$this->condition} {$this->groupby}) as t",$cache,$ttl,$this->dbid);
                $items = $t['items'];
            }else{
                $items = \DB::count($this->table,$this->condition,$ttl,$this->dbid);
            }
            if($items==0 && !$this->debug) return [];

            $pages = pages($items,$this->page,$this->pagesize);
        }
        $offset = ($this->page-1)*$this->pagesize;
        $limit = $this->limit ? "limit {$offset},{$this->pagesize}" : '';
        $condition = ($this->condition ? "WHERE " : '') .$this->condition;
        $sql = "select {$this->field} from {$this->table} {$condition} {$this->groupby} {$this->order} {$limit}";
        $result = \DB::query($sql,$cache,$ttl,$this->dbid);
        if($this->cache) $cacheid = $this->cache_id();
        if($this->debug) echo $sql;
        while ($r = \DB::fetch_array($result)){
            $lists[] = $r;
            if($this->showcat) $catids[$r['catid']] = $r['catid'];
        }
        if($catids && $this->showcat) {
            $result = \DB::query("SELECT catid,catname,linkurl FROM {$DT_PRE}category WHERE catid IN (".implode(',', $catids).")");
            while($r = \DB::fetch_array($result)) {
                if($this->moduleid>4){
                    $r['alt'] = $r['title'];
                    $r['linkurl'] = $MODULE[$this->moduleid]['linkurl'].$r['linkurl'];
                }
                $CATS[$r['catid']] = $r;
            }
            if($CATS) {
                foreach($lists as $k=>$v) {
                    $lists[$k]['catname'] = $v['catid'] ? $CATS[$v['catid']]['catname'] : '';
                    $lists[$k]['caturl'] = $v['catid'] ? $MODULE[$this->moduleid]['linkurl'].$CATS[$v['catid']]['linkurl'] : '';
                }
            }
        }
        return $lists;
    }

    public function get_one(){
        global $db;
        $this->sql();
        $cache = $this->cache ? "CACHE" : '';
        $ttl = $this->cache ? $this->cache : 0;
        $condition = ($this->condition ? "WHERE " : '') .$this->condition;
        $sql = "select {$this->field} from {$this->table} {$condition} {$this->groupby} {$this->order}";
        if($this->debug) echo $sql;
        return \DB::get_one($sql,$cache,$ttl,$this->dbid);
    }


    /*
     * insert into 方法 单条入库
     */
    public function insert($data=array(),$flag=false){
        global $db;
        if(empty($data)) return false;
        $data = dhtmlspecialchars($data);
        /*
         * 如果使用了关联副表插入，则先判断副表数据格式是否正确
         */
        if(is_array($this->content) && (count($this->content['data'])==0 || !$this->content['key'])){
            message('content()方法参数错误，终止执行本次操作');
            return false;
        }
        /*
         * end
         */
        $sqlk = $sqlv = '';
        foreach($data as $k=>$v){
            if($this->field!='*'){
                $fields = explode(',',$this->field);
                if(!in_array($k,$fields)) continue;
            }
            $sqlk .= ','.$k; $sqlv .= ",'$v'";
        }
        $sqlk = substr($sqlk, 1);
        $sqlv = substr($sqlv, 1);
        if($flag){ //replace 方法
            $sql = "REPLACE INTO {$this->table} ($sqlk) VALUES ($sqlv)";
            $row = \DB::query($sql,'',0,$this->dbid);
        }else{ //insert方法;
            $sql = "INSERT INTO {$this->table} ($sqlk) VALUES ($sqlv)";
            $row = \DB::query($sql,'',0,$this->dbid);
        }
        if($this->debug) echo $sql;
        $this->insert_id = \DB::insert_id();

        if($this->insert_id){
            //关联表数据插入,实现类似事务功能
            if($this->content) {
                $key = strip_tags($this->content['key']);
                $field = $this->content['field'];
                $table = $this->content['table'];
                $data = $this->content['data'];
                $replace = $this->content['replace'];
                $sqlk = $sqlv = '';
                if(count($data) == count($data, 1)) { //如果传过来的是1维数组，则单条入库
                    foreach($data as $k=>$v){
                        if($field){
                            if(!in_array($k,$field)) continue;
                            $sqlk .= ','.$k; $sqlv .= ",'$v'";
                        }
                    }
                    $sqlk = $key.$sqlk; //把主健连接在最开始
                    $sqlv = $this->insert_id.$sqlv; //连接主健值
                    if($replace){ //replace 方法
                        $content_row = \DB::query("REPLACE INTO {$table} ($sqlk) VALUES ($sqlv)",'',0,$this->dbid);
                    }else{ //insert方法
                        $content_row = \DB::query("INSERT INTO {$table} ($sqlk) VALUES ($sqlv)",'',0,$this->dbid);
                    }

                    if(!$content_row){ //如果副表插入失败，则执行回滚操作，删除原来插入的主表信息
                        $PRIMARY = \DB::get_one("SELECT column_name FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_name='".$this->table."' AND constraint_name='PRIMARY'",'',0,$this->dbid);
                        $columnname = $PRIMARY['column_name'];
                        \DB::query("delete from {$this->table} where $columnname=$this->insert_id",'',0,$this->dbid);
                        //删除当前insert id值
                        $this->insert_id = 0;
                        $row = 0;
                    }

                }else{ //如果是二维数组则使用批量插入
                    $this->field = $field;
                    $items = $this->insertAll($data, $table);
                    if($items!=count($data)){ //如果副表插入成功的数据和传过来的数据条数不一至，执行回撤工作
                        $PRIMARY = \DB::get_one("SELECT column_name FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_name='".$this->table."' AND constraint_name='PRIMARY'",'',0,$this->dbid);
                        $columnname = $PRIMARY['column_name'];
                        \DB::query("delete from {$this->table} where $columnname=$this->insert_id",'',0,$this->dbid);
                        \DB::query("delete from $table where $key=$this->insert_id",'',0,$this->dbid);
                        //删除当前insert id值
                        $this->insert_id = 0;
                        $row = 0;
                    }
                }

            }
        }else{
            if(!$row) message('插入失败');
        }
        return $this->insert_id ? $this->insert_id : $row;

    }

    /*
     * 批量插入
     */

    public function insertAll($data,$table=''){
        global $db;
        if(empty($data)) return false;
        $data = dhtmlspecialchars($data);
        $this->table = $table ? $table : $this->table;
        if(count($data)==count($data,1)) return $this->insert($data); //如果是一维数据，调用单条插入操作
        $sqlk = '';
        $values = array();
        foreach($data as $i=>$t){
            $sqlv = '';
            foreach($t as $k=>$v){
                if($this->field!='*'){
                    if(!in_array($k,$this->field)) continue;

                }
                if($i==0) $sqlk .= ','.$k;
                $sqlv .= ",'$v'";
            }
            if($this->content){ //如果副本联插模式
                $sqlv = $this->insert_id.$sqlv; //连接主健值
            }else{
                $sqlv = substr($sqlv,1);
            }
            $values[] = "(".$sqlv.")";
        }
        if($this->content){ //如果当前是副本联插
            $sqlk = $this->content['key'].$sqlk; //把主健连接在最开始
        }else{
            $sqlk = substr($sqlk, 1);
        }
        $sqlvalues = implode(',',$values);
        $sql = "INSERT INTO {$this->table} ($sqlk) VALUES $sqlvalues";
        $row = \DB::query($sql,'',0,$this->dbid);
        //返回插入成功的数量
        $num = $row;
        if($this->row){
            $r = \DB::get_one("select count(*) as num from {$this->table} where $this->content['key']=$this->insert_id",'',0,$this->dbid);
            $num = $r['num'];
        }
        if($this->debug) echo $sql;
        return $num;
    }

    /*
     * update 方法
     */
    public function update($data=[]){
        global $db;
        if(empty($data) && !$this->updatesql) message('请传入sql条件');
        $sql = '';
        foreach($data as $k=>$v){
            if($this->field!='*'){
                $fields = strstr($this->field,',') ? explode(',',$this->field) : array($this->field);
                if(!in_array($k,$fields)) continue;
            }
            $sql .= ','.$k."='".$v."'";
        }
        if($this->updatesql){
            $sql = $this->updatesql.$sql;
        }else{
            $sql = substr($sql,1);
        }
        $this->sql();
        $sql = "UPDATE {$this->table} set $sql where {$this->condition}";
        $flag = \DB::query($sql,'',0,$this->dbid);
        if($this->debug) echo $sql;
        return $flag;
    }

    /*
     * 删除
     */

    public function delete($itemid=0){
        global $db;
        if($itemid){
            $PRIMARY = \DB::get_one("SELECT column_name FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_name='".$this->table."' AND constraint_name='PRIMARY'",'',0,$this->dbid);
            $columnname = $PRIMARY['column_name'];
            $sql = "delete from {$this->table} where $columnname=$itemid";
            \DB::query($sql,'',0,$this->dbid);
        }else{
            $this->sql();
            $sql = "delete from {$this->table} where {$this->condition}";
            \DB::query($sql,'',0,$this->dbid);
            if($this->debug) echo $sql;
            return true;
        }

    }

    /*
     * 静态方法链式查询
     * select::table(表名)
     */
    public static function table($str){
        global $DT_PRE;
        $do = new self();
        $do->table = $DT_PRE.$str;
        return $do;
    }

    /*
     * 获取缓存
     * 该方法在默认的destoon系统中无效，需要更新mysqli.class.php
     */

    private function cache_id(){
        global $db;
        return $db->cid;
    }



}